﻿using System;
using System.Collections.Generic;
using Dapper;
using zhangmenren.Db;
using zhangmenren.Models;
using zhangmenren.Models.ViewModle;
namespace zhangmenren.Repository
{
    public class ChapterRepository:BaseRepository
    {
        public int Add(ChapterEntity entity)
        {
            string sql = @"INSERT INTO Chapter 
                            ([WorkId]
                            ,Title
                            ,[Url]
                            ,[Sort])
                        VALUES
                            (@WorkId
                            ,@Title
                            ,@Url
                            ,@Sort)";
            return Execute(connection => connection.Execute(sql, entity));
        }

        public IEnumerable<ChapterEntity> Select(int workId)
        {
            string sql = @"SELECT Id,WorkId,Title,Url,Sort FROM Chapter WHERE WorkId=@WorkId ORDER BY Sort DESC";
            return Execute(connection => connection.Query<ChapterEntity>(sql,new {WorkId=workId }));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
        }

        public IEnumerable<TuiJianModel> FindByWorkId(int workId)
        {
            string sql = @"SELECT Works.Id,Works.Title,Chapter.Title as ChapterTitle, Works.AuthorId,Works.Price,Works.CoverImg,Url,Chapter.[Sort],(select COUNT(Id) from Census where WorkId=@WorkId and Type=1) AS DianZhanNum,(select COUNT(Id) from Census where WorkId=@WorkId AND Type=0)AS ShouChangNum,(select COUNT(Id) from Census where WorkId=@WorkId and Type=2)as GuanZhuNum,(select COUNT(Id) from Census where WorkId=@WorkId and Type=3)as GouMaiNum FROM Chapter join Works on Chapter.WorkId=Works.Id WHERE WorkId=@WorkId ORDER BY Chapter.Sort DESC";
            return Execute(connection => connection.Query<TuiJianModel>(sql, new { WorkId = workId }));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
        }

        public int Del(int id)
        {
            string sql = @"DELETE FROM Chapter WHERE Id = @Id";
            return Execute(connection => connection.Execute(sql, new { Id = id }));
        }

    }
}
